import os
import logging
import traceback
import time

from tool_util import create_mysql_conn
from tool_util import get_mysql_resource_info
from tool_util import prometheus_push

from info_config import con_timeout
from info_config import mysql_response_time_prometheus_metrics_name
from info_config import mysql_locks_count_prometheus_metrics_name
from info_config import prometheus_url
from info_config import db_user
from info_config import db_password

# Get the current script path
script_path = os.path.dirname(os.path.abspath(__file__))
log_file = os.path.join(script_path, "mysql_response_monitor_all.log")

logging.basicConfig(
    filename=log_file,
    level=logging.INFO,
    format="%(asctime)s - %(levelname)s - %(message)s",
)


def find_largest_table(mysql_conn):
    excluded_databases = [
        "__recycle_bin__",
        "information_schema",
        "mysql",
        "performance_schema",
        "sys",
    ]
    try:
        with mysql_conn.cursor() as cursor:
            query = f"""
                SELECT 
                    table_schema, 
                    table_name
                FROM 
                    information_schema.tables
                WHERE 
                    table_schema NOT IN ({', '.join(['%s'] * len(excluded_databases))})
                ORDER BY 
                    data_length + index_length DESC
                LIMIT 1
            """
            cursor.execute(query, (*excluded_databases,))
            largest_table = cursor.fetchone()

            if largest_table:
                schema, name = largest_table
                logging.info(f"Largest Table - Database: {schema}, Table: {name}")
                return schema, name
            else:
                logging.info("No tables found.")
                return None, None
    except Exception as e:
        logging.error("Error finding the largest table:", e)
        return None, None


def get_mysql_response_time(mysql_host, mysql_port, dbname, large_table):
    """Connects to a specific database and retrieves response time for a query on the given large table."""
    conn = create_mysql_conn(
        mysql_host, mysql_port, db_user, db_password, timeout=con_timeout
    )
    if conn is None:
        logging.error(f"Could not connect to database {dbname}")
        return None

    try:
        with conn.cursor() as cursor:
            # Measure execution time for the query
            start_time = time.time()
            cursor.execute(f"SELECT * FROM {dbname}.{large_table} LIMIT 200;")
            cursor.fetchall()  # Fetch to complete execution
            response_time = (time.time() - start_time) * 1000  # Convert to milliseconds

            logging.info(
                f"Query executed in {response_time} seconds on {large_table} in {dbname}"
            )
            return response_time

    except Exception as e:
        logging.error(f"Error in get_mysql_response_time for {dbname}: {str(e)}")
        traceback.print_exc()
        return None
    finally:
        conn.close()


def mysql_response_monitor(mysql_info):
    """Monitors the PostgreSQL instance for response time on a specific large table."""
    id = mysql_info[0]
    name = mysql_info[1]
    mysql_host = mysql_info[2].get("host")
    mysql_port = int(mysql_info[2].get("port"))

    mysql_conn = create_mysql_conn(
        mysql_host, mysql_port, db_user, db_password, timeout=con_timeout
    )
    if mysql_conn is None:
        logging.error(
            "Could not connect to the postgres database to search for large tables."
        )
        return
    dbname, large_table = find_largest_table(mysql_conn)
    mysql_conn.close()
    if dbname and large_table:
        response_time = get_mysql_response_time(
            mysql_host, mysql_port, dbname, large_table
        )
        if response_time is not None:
            metric_data = f'{mysql_response_time_prometheus_metrics_name}{{instanceId="{id}" ,resourceName="{name}"}} {response_time}'
            prometheus_push(prometheus_url, metric_data)


def mysql_response_monitor_all(mysql_info_list):
    """Monitors all mysql instances listed in mysql_info_list."""
    for mysql_info in mysql_info_list:
        try:
            logging.info(f"Start monitoring {mysql_info}")
            mysql_response_monitor(mysql_info)
        except Exception as e:
            traceback.print_exc()


def get_mysql_lock_count(mysql_conn):
    try:
        with mysql_conn.cursor() as cursor:
            # Check MySQL version
            cursor.execute("SELECT VERSION()")
            version = cursor.fetchone()[0]

            if version.startswith("5.7"):
                # Count rows in INNODB_LOCK_WAITS for MySQL 5.7
                cursor.execute(
                    "SELECT COUNT(*) FROM information_schema.INNODB_LOCK_WAITS"
                )
                lock_count = cursor.fetchone()[0]
                return lock_count
            else:
                return None
    except Exception as e:
        logging.error("Error retrieving MySQL lock count:", e)
        return None


def mysql_innodb_locks_monitor(mysql_info):
    id = mysql_info[0]
    name = mysql_info[1]
    mysql_host = mysql_info[2].get("host")
    mysql_port = int(mysql_info[2].get("port"))

    mysql_conn = create_mysql_conn(
        mysql_host, mysql_port, db_user, db_password, timeout=con_timeout
    )
    if mysql_conn is None:
        logging.error(
            "Could not connect to the postgres database to search for large tables."
        )
        return
    lock_count = get_mysql_lock_count(mysql_conn)
    mysql_conn.close()
    if lock_count is None:
        logging.error("Error getting lock count.")
        return
    print(f"Lock count: {lock_count} ,{id} {name}")
    metric_data = f'{mysql_locks_count_prometheus_metrics_name}{{instanceId="{id}" ,resourceName="{name}"}} {lock_count}'
    prometheus_push(prometheus_url, metric_data)

def mysql_lock_monitor_all(mysql_info_list):
    """Monitors all mysql instances innodb locks listed in mysql_info_list."""
    for mysql_info in mysql_info_list:
        try:
            logging.info(f"Start monitoring {mysql_info}")
            mysql_innodb_locks_monitor(mysql_info)
        except Exception as e:
            traceback.print_exc()


def main():
    mysql_info_list = get_mysql_resource_info()
    mysql_response_monitor_all(mysql_info_list)
    mysql_lock_monitor_all(mysql_info_list)


if __name__ == "__main__":
    main()
